package xxxx.com.dao;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.RowProcessor;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import xxxx.com.annotation.Table;
import xxxx.com.dao.base.BaseDao;
import xxxx.com.model.User;
import xxxx.com.utils.JDBCUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import xxxx.com.utils.RefUtil;

import java.lang.reflect.Field;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

public class UserDao extends BaseDao<User> {


    /**
     * 用户登录的sql（暂定）
     *
     * @param username 用户名
     * @return User对象
     */
    public User findUserByName(String username) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        //开启下划线->驼峰转换所用
        RowProcessor processor = new BasicRowProcessor(new GenerousBeanProcessor());
        User user = null;
        try {
            user = qr.query("select * from user where username = ? limit 1", new BeanHandler<>(User.class,processor), username);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    /**
     * 注册用户信息
     *
     * @param user
     * @return true成功添加，false添加失败
     */
    public boolean addUser(User user) {
        Object[] params = RefUtil.getParams(user);
        String tableName = RefUtil.getTableName(user.getClass());
        StringBuilder sb = new StringBuilder("insert into " + tableName + " values(");
        for (int i = 0; i < params.length; i++) {
            sb.append("?,");
            if (i == params.length - 1) {
                sb.replace(sb.length() - 1, sb.length(), ");");
            }
        }
        if (adu(sb.toString(), params) > 0) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 通过E-mail查找用户注册信息（user表）
     * @param email
     * @return
     */
    public User findUserByEmail(String email){
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        User user = null;
        try {
            user = qr.query("select * from user where email = ? limit 1", new BeanHandler<>(User.class), email);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    /**
     * 通过id，找到对应的用户并修改其密码
     * @param id
     * @param p
     */
    public void resetPassword(Integer id,String p){
//        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        String sql = "update user set password = ? where id = ?;";
        Object[] obj = {p,id};
        adu(sql,obj);
    }

    /**
     * 根据id连表查找用户的信息，封装成Map集合
     * @param id
     * @return
     */
    public Map<String, Object> findUserBaseInfoById(Integer id) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        String sql = "select u.id,u.username,u.dept_id as deptId,u.is_secret as isSecret,ui.pic,d.`name` as deptName\n" +
                "from user u left join dept d on u.dept_id = d.id\n" +
                "left join user_info ui on u.id = ui.user_id\n" +
                "where u.id = ?;";
        Map<String, Object> map = null;
        try {
            map = qr.query(sql, new MapHandler(), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return map;
    }

    /**
     * 根据传入的Object数组的长度，确定修改的内容
     * @param obj
     */
    public void updateSecret(Object[] obj) {
        String sql;
        if (obj.length == 2){
            sql = "update user set is_secret = ? where id = ?;";
        }else {
            sql = "update user set is_secret = ?,dept_id = ? where id = ?";
        }
        adu(sql,obj);
    }

    /**
     * 通过用户ID查找用户的username和realName
     * @param uId
     * @return
     */
    public Map<String, Object> getNameById(Integer uId) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        Map<String,Object> map = null;
        try{
            String sql = "select u.username,ui.real_name as realName from user u left join user_info ui on u.id = ui.user_id where u.id = ?;";
            map = qr.query(sql,new MapHandler(),uId);
        }catch (SQLException e){
            e.printStackTrace();
        }
        return map;
    }

    /**
     * 获取各部门中员工数目
     * @return
     */
    public List<Map<String,Object>> getUserCount() {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        String sql = "select u.dept_id as deptId, count(*) as total\n" +
                "from user u\n" +
                "group by dept_id";
        List<Map<String,Object>> list = null;
        try {
            list = qr.query(sql,new MapListHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 通过部门ID查找属于该部门的员工ID
     * @param deptID
     * @return
     */
    public List<Map<String, Object>> getUserId(Integer deptID) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        List<Map<String,Object>> list = null;
        String sql = "select id from user where dept_id = ?;";
        try {
            list = qr.query(sql,new MapListHandler(),deptID);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}

